﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Codebricks.Db;
using KBVault.DAL.Entities;
using System.Data;
using Codebricks.Util;

namespace KBVault.DAL.Repository
{
    public class UserRepository : BaseRepository
    {
        private static string InsertCommand = "Insert Into Users(user_name,password,user_type,name,last_name,email) Values(@UserName,@Password,@UserType,@Name,@LastName,@Email)";
        private static string DeleteCommand = "Delete From Users Where id = @Id";
        private static string UpdateCommand = "Update Users Set user_name=@UserName,password=@Password,user_type=@UserType,name=@Name,last_name=@LastName,email=@Email where id = @Id";
        private static string SelectByUserNameCommand = "Select * From Users Where user_name=@UserName";
        private static string SelectByIdCommand = "Select * From Users Where id=@Id";
        private static string ListCommand = "Select * From Users Order by user_name";
        private static string SetUserLoggedInCommand = "Update users set user_logged_in = @LogInStatus where id = @UserId";
        private static string LogoffAllUsersCommand = "Update users set user_logged_in = 0";

        public static User Create(string userName, string password, int userType, string name = "", string lastName = "", string email = "")
        {
            try
            {
                User newUser = new User
                {
                    Password = password,
                    UserName = userName,
                    UserType = userType,
                    Name = name,
                    LastName = lastName,
                    Email = email
                };


                MysqlParameterCollection collection = new MysqlParameterCollection();
                collection.Add("UserName", newUser.UserName);
                collection.Add("Password", Crypto.Md5(newUser.Password));
                collection.Add("UserType", newUser.UserType);
                collection.Add("Name", newUser.Name);
                collection.Add("LastName", newUser.LastName);
                collection.Add("Email", newUser.Email);
                newUser.Id = MySqlDb.Insert(InsertCommand, collection);
                return newUser;
            }
            catch (Exception ex)
            {
                Log.Error(ex.Message);
                throw;
            }
        }

        private static User CreateUserFromDataRow(DataRow row)
        {
            User usr = new User();
            usr.Name = row["name"].ToString();
            usr.LastName = row["last_name"].ToString();
            usr.Email = row["email"].ToString();
            usr.UserName = row["user_name"].ToString();
            usr.Password = row["password"].ToString();
            usr.UserType = Convert.ToInt32(row["user_type"]);
            usr.Id = Convert.ToInt32(row["id"]);
            usr.IsLoggedIn = Convert.ToInt32(row["user_logged_in"]) == 1;
            return usr;
        }

        public static User Get(long id)
        {
            try
            {
                MysqlParameterCollection collection = new MysqlParameterCollection();
                collection.Add("Id", id);
                DataTable reader = MySqlDb.Select(SelectByIdCommand, collection);
                if (reader.Rows.Count == 1)
                {
                    return CreateUserFromDataRow(reader.Rows[0]);
                }
                return null;
            }
            catch (Exception ex)
            {
                Log.Error(ex.Message);
                throw;
            }
        }

        public static User Get(string userName)
        {
            try
            {
                MysqlParameterCollection collection = new MysqlParameterCollection();
                collection.Add("UserName", userName);
                DataTable reader = MySqlDb.Select(SelectByUserNameCommand, collection);
                if (reader.Rows.Count == 1)
                {
                    return CreateUserFromDataRow(reader.Rows[0]);
                }
                return null;
            }
            catch (Exception ex)
            {
                Log.Error(ex.Message);
                throw;
            }
        }

        public static void Delete(long Id)
        {
            try
            {
                MysqlParameterCollection collection = new MysqlParameterCollection();
                collection.Add("Id", Id);
                MySqlDb.ExecuteQuery(DeleteCommand, collection);
            }
            catch (Exception ex)
            {
                Log.Error(ex.Message);
                throw;
            }
        }

        public static void Update(User usr, bool updatePassword = false)
        {
            try
            {
                MysqlParameterCollection collection = new MysqlParameterCollection();
                collection.Add("Name", usr.Name);
                collection.Add("LastName", usr.LastName);
                collection.Add("Email", usr.Email);
                collection.Add("UserName", usr.UserName);
                if (updatePassword)
                    collection.Add("Password", Crypto.Md5(usr.Password));
                else
                    collection.Add("Password", usr.Password);
                collection.Add("UserType", usr.UserType);
                collection.Add("Id", usr.Id);
                MySqlDb.Insert(UpdateCommand, collection);
            }
            catch (Exception ex)
            {
                Log.Error(ex.Message);
                throw;
            }
        }

        public static bool Validate(string userName, string password)
        {            
            User usr = Get(userName);
            if (usr == null)
                return false;
            return Crypto.ValidateMd5(password, usr.Password);
        }

        public static List<User> List()
        {
            try
            {
                DataTable dt = MySqlDb.Select(ListCommand, null);
                List<User> resultSet = new List<User>();
                foreach (DataRow row in dt.Rows)
                {
                    resultSet.Add(Get(Convert.ToInt32(row["id"])));
                }
                return resultSet;
            }
            catch (Exception ex)
            {
                Log.Error(ex.Message);
                throw;
            }
        }

        private static void SetUserLoggedInStatus(User usr, int status)
        {
            try
            {
                MysqlParameterCollection parameters = new MysqlParameterCollection();
                parameters.Add("UserId", usr.Id);
                parameters.Add("LogInStatus", status);
                MySqlDb.ExecuteQuery(SetUserLoggedInCommand, parameters);

            }
            catch (Exception ex)
            {
                Log.Error(ex.Message);
                throw;
            }
        }

        public static void SetUserLoggedIn(User usr)
        {
            SetUserLoggedInStatus(usr, 1);
        }
        public static void SetUserLoggedOff(User usr)
        {
            SetUserLoggedInStatus(usr, 0);
        }

        public static void LogoffAllUsers()
        {
            try
            {
                MySqlDb.ExecuteQuery(LogoffAllUsersCommand, null);
            }
            catch (Exception ex)
            {
                Log.Error(ex.Message);
                throw;
            }
        }
    }
}
